package com.viewsky.base.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Component;

import com.viewsky.base.config.Constant;

@Component("redisLoader")
public class RedisLoader
{
	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Resource(name="redisTemplate")
	private RedisTemplate<String, Object> redisTemplate;
	
	private final Logger logger = LoggerFactory.getLogger(this.getClass());
	
	public int loadUser() throws Exception
	{
		StringBuilder sbSql = new StringBuilder();
		
		/* mysql */
		sbSql.append("select 'USER' as 'key', user_id as 'field', user_name as 'value'");
		sbSql.append("  from t_sys_user order by user_id limit ?, ?");
		
		try
		{
			Map<String, Integer> mapResult = load(sbSql);	
			
			logger.info("用户信息共有" + mapResult.get("total") +
					"条，加载：" + mapResult.get("load") + "条。");	
			
			return mapResult.get("load");
		}
		catch(Exception e)
		{
			String strMessage = "加载用户信息出错：" + e.toString();
			
			logger.error(strMessage);
			throw new Exception(strMessage);
		}		
	}
	
	public int loadOrganization() throws Exception
	{		
		StringBuilder sbSql = new StringBuilder();

		/* mysql */
		sbSql.append("select 'ORG' as 'key', org_id as 'field', org_name as 'value'");
		sbSql.append("  from t_sys_org order by org_id limit ?, ?");
		
		try
		{
			Map<String, Integer> mapResult = load(sbSql);
			
			logger.info("机构信息共有" + mapResult.get("total") +
					"条，加载：" + mapResult.get("load") + "条。");	
			
			return mapResult.get("load");
		}
		catch(Exception e)
		{
			String strMessage =  "加载机构信息出错：" + e.toString();
			
			logger.error(strMessage);
			throw new Exception(strMessage);
		}
	}
	
	public int loadCodeInfo() throws Exception
	{
		StringBuilder sbSql = new StringBuilder();

		/* mysql */
		sbSql.append("select upper(code_type_id) as 'key', upper(value) as 'field', content as 'value'");
		sbSql.append("  from t_sys_code_info order by code_type_id, sort_no, code_info_id limit ?, ?");
		
		try
		{
			Map<String, Integer> mapResult = load(sbSql);
			
			logger.info("码表信息共有" + mapResult.get("total") +
					"条，加载：" + mapResult.get("load") + "条。");	
			
			return mapResult.get("load");
		}
		catch(Exception e)
		{
			String strMessage =  "加载机构信息出错：" + e.toString();
			
			logger.error(strMessage);
			throw new Exception(strMessage);
		}		
	}
	
	private Map<String, Integer> load(StringBuilder sbSql) throws Exception
	{
		/* 数据库里数据条数 */
		int iDataCount = 0;
		
		/* 加载到redis里数据的条数 */
		int iLoadCount = 0;
		
		Map<String, Integer> mapResult = new HashMap<>();
		List<Map<String, String>> listData = new ArrayList<>();
		
		int iStart = 0;
		String strLastKey = "";
		
		while(true)
		{
			/* 记录执行语句 */
			String strSql = sbSql.toString().replaceAll("\\s+", " ");
			strSql = strSql.replaceFirst("\\?", Integer.toString(iStart))
							.replaceFirst("\\?", Integer.toString(Constant.PAGE_SIZE));
			
			logger.info(strSql);
			
			/* 执行sql */
			listData = jdbcTemplate.query(
				new PreparedStatementCreator()
				{			
					@Override
					public PreparedStatement createPreparedStatement(
							Connection con) throws SQLException
					{
						return con.prepareStatement(sbSql.toString());
					}
				},
				new PageSetter(iStart),
				new ResultSetExtractor<List<Map<String, String>>>()
				{			
					@Override
					public List<Map<String, String>> extractData(ResultSet rs)
							throws SQLException, DataAccessException
					{
						List<Map<String, String>> listResult = new ArrayList<>();
						
						while(rs.next())
						{				
							Map<String, String> mapResult = new HashMap<>();
							
							mapResult.put("key", rs.getString("key"));
							mapResult.put("field", rs.getString("field"));
							mapResult.put("value", rs.getString("value"));
							
							listResult.add(mapResult);
						}
						
						return listResult;
					}		
				}
			);
			
			for(Map<String, String> mapData : listData)
			{
				String strKey = mapData.get("key");
				String strField = mapData.get("field");
				String strValue = mapData.get("value");	
				
				iDataCount++;

				if(strKey != null && strField != null && strValue != null)
				{
					redisTemplate.opsForHash().put(strKey, strField, strValue);
					iLoadCount++;
				}
				
				/* key改变了，就设置上一个的超时时间 */
				if(!strLastKey.equals(strKey))
				{
					if(!strLastKey.equals(""))
					{
						redisTemplate.expire(strLastKey, Constant.EXPIRE_SECOND, TimeUnit.SECONDS);
					}
					
					strLastKey = strKey;
				}
			}
			
			iStart += Constant.PAGE_SIZE;
			if(listData.size() < iStart)
			{
				break;
			}
		}
		
		/* 如果key固定，此处设置超时；key不固定，则此处设置最后一个key的超时时间 */
		redisTemplate.expire(strLastKey, Constant.EXPIRE_SECOND, TimeUnit.SECONDS);
		
		mapResult.put("total", iDataCount);
		mapResult.put("load", iLoadCount);
		
		return mapResult;
	}
}

class PageSetter implements PreparedStatementSetter
{
	private int iStart;
	
	public PageSetter()
	{
		
	}
	
	public PageSetter(int iStart)
	{
		this.iStart = iStart;
	}
	
	@Override
	public void setValues(PreparedStatement ps) throws SQLException
	{
		ps.setInt(1, iStart);
		ps.setInt(2, Constant.PAGE_SIZE);
	}
}
